// ============================================================================
//
// Copyright (C) 2006-2007 Dengues
//
// Google Group: http://groups.google.com/group/dengues
// QQ Group: 24885404
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
//
// ============================================================================
package org.dengues.core.sql;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.dengues.core.components.ENodeParameterName;
import org.dengues.model.database.DBColumn;
import org.dengues.model.database.DBTable;
import org.dengues.model.database.DatabaseDiagram;
import org.dengues.model.database.DiagramChild;
import org.dengues.model.database.ForeignKey;
import org.dengues.model.database.SectionColumns;
import org.dengues.model.database.SectionFilters;
import org.dengues.model.database.SectionGroups;
import org.dengues.model.database.SectionHavings;
import org.dengues.model.database.SectionSorts;
import org.dengues.model.database.SectionUnions;
import org.eclipse.emf.common.util.EList;

/**
 * Qiang.Zhang.Adolf@gmail.com class global comment. Detailled comment <br/>
 * 
 * $Id: Dengues.epf 2007-12-4 qiang.zhang $
 * 
 */
public class SqlUtils {

    /**
     * 
     */
    private static final String WHERE_KEY = "\nWHERE ";

    private static final String AND_KEY = " AND ";

    public static final String FK_OPERATOR = "FK_OPERATOR";

    public static final String FK_JOIN = "FK_JOIN";

    public static final String FK_SQLTYPE = "FK_SQLTYPE";

    public static final String FK_SQL92 = "FK_SQL92";

    public static final String FK_USEADD = "FK_USEADD";

    public static final String FK_LJOIN = " LEFT JOIN ";

    public static final String FK_RJOIN = " RIGHT JOIN ";

    public static final String FK_FJOIN = " FULL JOIN ";

    public static final String FK_NJOIN = "NONE JOIN";

    public static final String[] FK_OPERATORS = new String[] { "=", ">", "<", ">=", "<=", "<>" };

    private static final String COLUMN_SEPERATOR = ".";

    public static final String VALUE_SQL = "VALUE";

    private final static String SPACE = " "; //$NON-NLS-1$

    private final static String CREATE = "CREATE "; //$NON-NLS-1$

    private final static String TABLE = " TABLE "; //$NON-NLS-1$

    private final static String PK = "PRIMARY KEY ("; //$NON-NLS-1$

    private final static String FK = " \n\tFOREIGN KEY ("; //$NON-NLS-1$

    private final static String REF = " REFERENCES "; //$NON-NLS-1$

    public final static String[] TABLE_OPERATIONS = new String[] { "CREATE", "ALTER", "DROP" };

    public final static String[] COLUMN_OPERATIONS = new String[] { "ADD", "DROP" };

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getSqlStatement".
     * 
     * @param diagram
     * @return
     */
    public static String getSqlStatement(DatabaseDiagram diagram) {
        StringBuffer sql = new StringBuffer(""); //$NON-NLS-1$
        EList<DiagramChild> dbChildren = diagram.getDbChildren();
        for (DiagramChild diagramChild : dbChildren) {
            if (diagramChild instanceof DBTable) {
                List<String> pkeys = new ArrayList<String>();
                Map<String, DBColumn> fkeys = new HashMap<String, DBColumn>();

                DBTable table = (DBTable) diagramChild;
                Object operation = table.getProperties().get(ENodeParameterName.DB_TABLE_OPERATION.getName());
                if (operation instanceof String) {
                    sql.append(operation.toString());
                    sql.append(SPACE);
                } else {
                    sql.append(CREATE);
                }
                if (TABLE_OPERATIONS[0].equals(operation)) {
                    Object type = table.getProperties().get(ENodeParameterName.DB_TABLE_TYPE.getName());
                    if (type instanceof String) {
                        sql.append(type.toString());
                        sql.append(TABLE);
                        sql.append(SPACE);
                    } else {
                        sql.append(TABLE);
                    }
                } else {
                    sql.append(TABLE);
                }

                String tableName = table.getName();
                sql.append(tableName);
                if (TABLE_OPERATIONS[2].equals(operation)) {
                    sql.append(";\n"); //$NON-NLS-1$
                    continue;
                }
                EList<DBColumn> columns = table.getColumns();
                if (columns.isEmpty()) {
                    sql.append("(test int);\n"); //$NON-NLS-1$
                } else {
                    if (TABLE_OPERATIONS[1].equals(operation)) {
                        int i = 0;
                        String delStr = sql.toString();
                        for (DBColumn column : columns) {
                            Object object = column.getProperties().get(ENodeParameterName.DB_COLUMN_OPERATION.getName());
                            if (!column.isKey()) {
                                if (i > 0) {
                                    sql.append(delStr);
                                }
                                i++;
                                if (COLUMN_OPERATIONS[0].equals(object)) {
                                    sql.append(" " + object.toString() + " ");//$NON-NLS-1$
                                    sql.append(column.getName() + " " + getColumnType(column)); //$NON-NLS-1$ //$NON-NLS-2$
                                } else if (COLUMN_OPERATIONS[1].equals(object)) {
                                    sql.append(" " + object.toString() + " ");//$NON-NLS-1$ //$NON-NLS-2$
                                    sql.append(column.getName()); //$NON-NLS-1$
                                }
                                sql.append(";\n"); //$NON-NLS-1$
                            }
                        }
                    } else {
                        sql.append("("); //$NON-NLS-1$
                        for (DBColumn column : columns) {
                            sql.append(column.getName() + " " + getColumnType(column) + ","); //$NON-NLS-1$ //$NON-NLS-2$
                            if (column.isKey()) {
                                pkeys.add(column.getName());
                            }
                            for (ForeignKey fk : (List<ForeignKey>) column.getOutputs()) {
                                fkeys.put(column.getName(), fk.getTarget());
                            }
                        }
                        if (!pkeys.isEmpty()) {
                            sql.append(PK);
                            for (String string : pkeys) {
                                sql.append(string + ","); //$NON-NLS-1$
                            }
                            sql.deleteCharAt(sql.lastIndexOf(",")); //$NON-NLS-1$
                            sql.append(")"); //$NON-NLS-1$
                        }
                        sql.append(","); //$NON-NLS-1$
                        if (!fkeys.isEmpty()) {
                            sql.append(FK);
                        }
                        for (String fk : fkeys.keySet()) {
                            sql.append(fk + ")"); //$NON-NLS-1$
                            sql.append(REF);
                            DBTable t = fkeys.get(fk).getTable();
                            sql.append(t.getName() + "("); //$NON-NLS-1$
                            pkeys.clear();
                            for (DBColumn col : (List<DBColumn>) t.getColumns()) {
                                if (col.isKey()) {
                                    pkeys.add(col.getName());
                                }
                            }
                            for (String string : pkeys) {
                                sql.append(string + ","); //$NON-NLS-1$
                            }
                            sql.deleteCharAt(sql.lastIndexOf(",")); //$NON-NLS-1$
                            sql.append("),"); //$NON-NLS-1$
                        }
                        if (!columns.isEmpty()) {
                            sql.deleteCharAt(sql.lastIndexOf(",")); //$NON-NLS-1$
                        }
                        sql.append(");\n"); //$NON-NLS-1$
                    }
                }
            }
        }
        return sql.toString();
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getColumnType".
     * 
     * @param column
     * @return
     */
    private static String getColumnType(DBColumn column) {
        StringBuilder builder = new StringBuilder();
        builder.append(column.getType());
        if (column.getLength() > 0) {
            builder.append("(");
            builder.append(column.getLength());
            if (column.getPrecision() > 0) {
                builder.append(",");
                builder.append(column.getPrecision());
            }
            builder.append(")");
        }
        return builder.toString();
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getSelectStatement".
     * 
     * @param diagram
     * @return
     */
    public static String getSelectStatement(DatabaseDiagram diagram) {
        StringBuffer builder = new StringBuffer();
        StringBuffer columslist = new StringBuffer();
        StringBuffer tableString = new StringBuffer();
        StringBuffer whereString = new StringBuffer();
        List<String> joinTables = new ArrayList<String>();
        List<String> allTables = new ArrayList<String>();

        EList dbChildren = diagram.getDbChildren();
        for (Object object : dbChildren) {
            if (object instanceof DBTable) {
                StringBuffer tempString = new StringBuffer();
                DBTable table = (DBTable) object;
                boolean equals = false;
                EList columns = table.getColumns();
                allTables.add(table.getName());
                String fkjoin = "";
                String tableJoin = "";
                ForeignKey hasforeignKey = null;
                for (Object object2 : columns) {
                    DBColumn column = (DBColumn) object2;
                    if (column.isSelected()) {
                        columslist.append(getAllColumnName(column) + ",");
                    }
                    EList outputs = column.getOutputs();
                    for (Object object3 : outputs) {
                        ForeignKey foreignKey = (ForeignKey) object3;
                        Object oper = foreignKey.getProperties().get(FK_OPERATOR);
                        tempString.append(getAllColumnName(column) + (oper != null ? oper.toString() : FK_OPERATORS[0]));
                        tempString.append(getAllColumnName(foreignKey.getTarget()));
                        tempString.append(AND_KEY);
                        boolean b = (foreignKey.getProperties().get(FK_JOIN) != null && !FK_NJOIN.equals(foreignKey
                                .getProperties().get(FK_JOIN)));
                        equals = equals || b;
                        if (b && (hasforeignKey == null || hasforeignKey == foreignKey)) {
                            fkjoin = foreignKey.getProperties().get(FK_JOIN).toString();
                            tableJoin = foreignKey.getTarget().getTable().getName();
                            hasforeignKey = foreignKey;
                        } else {
                            whereString.append(tempString);
                        }
                    }
                }
                if (!equals) {
                    // if (!joinTables.contains(table.getName())) {
                    // tableString.append(table.getName() + ",");
                    // }
                } else {
                    tempString.insert(0, '(');
                    tempString.delete(tempString.lastIndexOf(AND_KEY), tempString.length() - 1);
                    tempString.append(") ");
                    tableString.append(table.getName() + " " + fkjoin + " " + tableJoin + " ON " + tempString + ",");
                    joinTables.add(tableJoin);
                    joinTables.add(table.getName());
                }
            }
        }
        for (String string : allTables) {
            if (!joinTables.contains(string)) {
                tableString.append(string + ",");
            }
        }
        int index = tableString.length() - 1;
        if (index > -1 && tableString.charAt(index) == ',') {
            tableString.deleteCharAt(index);
        }
        getSelectSubSQL(diagram, builder, columslist);
        getFromSubSQL(builder, tableString);
        getWhersSubSQL(diagram, builder, whereString);
        getGroupsSubSQL(diagram, builder);
        getSortsSubSQL(diagram, builder);
        getHavingsSubSQL(diagram, builder);
        getUnionsSubSQL(diagram, builder);
        return builder.toString();
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getSelectSubSQL".
     * 
     * @param diagram
     * @param builder
     * @param columslist
     */
    private static void getSelectSubSQL(DatabaseDiagram diagram, StringBuffer builder, StringBuffer columslist) {
        builder.append("SELECT ");
        if (diagram.isDistinct()) {
            builder.append(" DISTINCT ");
        }
        builder.append(columslist);
        EList sectionColumns = diagram.getSectionColumns();
        for (Object object : sectionColumns) {
            SectionColumns columns = (SectionColumns) object;
            builder.append(columns.getExpression());
            builder.append(" AS " + columns.getName() + ",");
        }
        int index = builder.length() - 1;
        if (index > -1 && builder.charAt(index) == ',') {
            builder.deleteCharAt(index);
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getFromSubSQL".
     * 
     * @param builder
     * @param tableString
     */
    private static void getFromSubSQL(StringBuffer builder, StringBuffer tableString) {
        builder.append("\nFROM ");
        builder.append(tableString);
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getWhersSubSQL".
     * 
     * @param diagram
     * @param builder
     * @param whereString
     */
    private static void getWhersSubSQL(DatabaseDiagram diagram, StringBuffer builder, StringBuffer whereString) {
        int index;
        StringBuffer whereString2 = new StringBuffer();
        EList sectionFilters = diagram.getSectionFilters();
        int lastLength = AND_KEY.length() + 1;
        if (!sectionFilters.isEmpty()) {
            whereString2.append("( ");
            for (Object object4 : sectionFilters) {
                SectionFilters filters = (SectionFilters) object4;
                whereString2.append(filters.getName());
                whereString2.append(" " + filters.getOperator() + " ");
                whereString2.append(filters.getProperties().get(VALUE_SQL));
                whereString2.append(" " + filters.getLogic() + " ");
                lastLength = filters.getLogic().length() + 3;
            }
            whereString2.append(" )");
            index = whereString2.length() - lastLength;
            whereString2.delete(index, whereString2.length() - 1);
        } else {
            if (whereString.length() > AND_KEY.length()) {
                whereString.delete(whereString.length() - AND_KEY.length(), whereString.length() - 1);
            }
        }
        if (whereString.length() + whereString2.length() > 0) {
            builder.append(WHERE_KEY);
            builder.append(whereString);
            builder.append(whereString2);
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getUnionsSubSQL".
     * 
     * @param diagram
     * @param builder
     */
    private static void getUnionsSubSQL(DatabaseDiagram diagram, StringBuffer builder) {
        EList sectionUnions = diagram.getSectionUnions();
        for (Object object : sectionUnions) {
            SectionUnions sectionUnions2 = (SectionUnions) object;
            if (sectionUnions2.isChecked()) {
                builder.append("\n" + sectionUnions2.getUnion() + " ");
                builder.append(sectionUnions2.getQueries());
            }
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getHavingsSubSQL".
     * 
     * @param diagram
     * @param builder
     */
    private static void getHavingsSubSQL(DatabaseDiagram diagram, StringBuffer builder) {
        int index;
        int lastLength;
        EList sectionHavings = diagram.getSectionHavings();
        if (!sectionHavings.isEmpty()) {
            builder.append("\nHAVING ");
            lastLength = AND_KEY.length();
            for (Object object2 : sectionHavings) {
                SectionHavings sectionHavings2 = ((SectionHavings) object2);
                builder.append(sectionHavings2.getName());
                builder.append(" " + sectionHavings2.getOperator() + " ");
                builder.append(sectionHavings2.getValue());
                builder.append(" " + sectionHavings2.getLogic() + " ");
                lastLength = sectionHavings2.getLogic().length() + 2;
            }
            index = builder.length() - lastLength;
            builder.delete(index, builder.length() - 1);
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getSortsSubSQL".
     * 
     * @param diagram
     * @param builder
     */
    private static void getSortsSubSQL(DatabaseDiagram diagram, StringBuffer builder) {
        int index;
        EList sectionSorts = diagram.getSectionSorts();
        if (!sectionSorts.isEmpty()) {
            builder.append("\nORDER BY ");
            for (Object object2 : sectionSorts) {
                builder.append(((SectionSorts) object2).getExpression() + " ");
                builder.append(((SectionSorts) object2).getOrderBy() + ",");
            }
            index = builder.length() - 1;
            if (index > -1 && builder.charAt(index) == ',') {
                builder.deleteCharAt(index);
            }
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getGroupsSubSQL".
     * 
     * @param diagram
     * @param builder
     */
    private static void getGroupsSubSQL(DatabaseDiagram diagram, StringBuffer builder) {
        int index;
        EList sectionGroups = diagram.getSectionGroups();
        if (!sectionGroups.isEmpty()) {
            builder.append("\nGROUP BY ");
            for (Object object2 : sectionGroups) {
                builder.append(((SectionGroups) object2).getExpression() + ",");
            }
            index = builder.length() - 1;
            if (index > -1 && builder.charAt(index) == ',') {
                builder.deleteCharAt(index);
            }
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getAllColumnName".
     * 
     * @param column
     * @return
     */
    public static String getAllColumnName(DBColumn column) {
        return column.getTable().getName() + COLUMN_SEPERATOR + column.getName();
    }
}
